とある古典的SQLおじさんのBigQuery入門：ARRAY型とSTRUCT型

「1つのフィールドにはデータを1つだけ格納しなさい」そう言われて育ちました。

アドベントカレンダー2020

#Google BigQuery

#Google Cloud (GCP)

甲木洋介

2020.12.17

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

本エントリは、クラスメソッド BigQuery Advent Calendar 2020 の 17 日目のエントリです。
25日のアドベントカレンダー終了まで、弊社クラスメソッドデータアナリティクス事業本部のメンバーで、Google BigQuery に関する記事を紡いでいこうと思います。

多くのリレーショナルデータベース(RDB)およびRDBをベースにしたデータウェアハウスにおいて、

データはテーブルに格納する
テーブルはカラム(列)とレコード(行)で構成される
カラムとレコードが交わるフィールドには、1つのデータが格納される
各カラムは固定の型を持ち、1つのカラムに異なる型のデータを格納できない

などの原則があることをご存知の方は多いと思います。

BigQueryには、標準SQLのデータ型として、INTやVARCHARに加えてARRAYやSTRUCTなど、複雑な構造のデータを格納する型が提供されています。今まであまり馴染みのなかったこれらデータ型について理解を深めます。

ARRAY型とは

ARRAY型とは、ゼロ個以上の同じデータ型の値で構成された順序付きリストのことです。日本語では「配列」といいます。

参考：標準SQLのデータ型｜配列型

例えば、以下のゲーム機の名前をARRAY型に格納してみます。

SELECT ['Xbox One', 'Xbox Series X', 'Xbox Series S'] AS consoles;

実行結果は以下です。ご覧のように1行の3つのデータが格納されている事が確認できます。

ちなみにBigQueryの標準SQLにおいては、WITH句を用いて名前付きサブクエリを作成できるようになっています。WITH句で指定したサブクエリの結果が、あたかも実テーブルやビューであるかのように扱えます。

参考：標準SQLへの移行｜WITH句を使用したコンポーザビリティ

WITH consoles AS (
  SELECT ['Xbox One', 'Xbox Series X', 'Xbox Series S'] AS console_name
)
SELECT * FROM consoles;

また、OFFSET()やORDINAL()などの関数を使って、配列内のそれぞれの要素に対して操作することが可能です。

WITH consoles AS (
  SELECT ['Xbox One', 'Xbox Series X', 'Xbox Series S'] AS console_name
)
SELECT
  console_name[OFFSET(2)], --0オリジンで数える(=3つ目の要素)
  console_name[ORDINAL(2)] --1オリジンで数える(=2つ目の要素)
FROM consoles;

STRUCT型とは

STRUCT型とは、1つもしくは複数の値をまとめて格納できる構造のことです。日本語では「構造体」といいます。

参考：標準SQLのデータ型｜構造体型

ゲーム機を提供しているベンダーとゲーム機の情報をSTRUCT型で格納します。

WITH vendors AS (
  SELECT STRUCT(
    'Microsoft' AS vendor_name,
    'Xbox Series X' AS console_name
  ) AS vendor_set
)
SELECT * FROM vendors;

STRUCT型の中にARRAY型を包含する事が可能です。ベンダー1社につきゲーム機は複数提供されているので、ベンダーごとのゲーム機をARRAY型に格納します。

WITH vendor AS (
  SELECT STRUCT(
    'Microsoft' AS name,
    ['Xbox One', 'Xbox Series X', 'Xbox Series S'] AS consoles
  ) AS vendor_set
)
SELECT * FROM vendor;

このようにして、複数のベンダーと、ベンダーが提供するゲーム機の情報を、1つのテーブルに格納する事が可能です。 3件のベンダー、ベンダーに紐づく9件のゲーム機のデータを3行のデータとして格納しています。

WITH vendors AS (
  SELECT STRUCT(
    'Microsoft' AS name,
    ['Xbox One', 'Xbox Series X', 'Xbox Series S'] AS consoles
  ) AS vendor_set
  UNION ALL
  SELECT STRUCT(
    'Nintendo' AS name,
    ['Wii U', 'Nintendo Switch', 'Nintendo Switch Lite'] AS consoles
  ) AS vendor_set
  UNION ALL
  SELECT STRUCT(
    'Sony' AS name,
    ['Playstation 3', 'Playstation 4', 'Playstation 5'] AS consoles
  ) AS vendor_set 
)
SELECT * FROM vendors;

普通の…普通のテーブルが欲しい！

ここまでで、BigQueryはかなり自由な構造でデータを格納する事ができるということが理解できましたが、1つのフィールドに複数のデータが含まれているのは、古典的なSQLおじさんとしてはやっぱり気持ち悪い。
そこで、UNNEST()関数を用いて配列をバラし、9行のデータに展開します。

参考：標準SQLでの配列の操作｜配列のフラット化

WITH vendors AS (
  SELECT STRUCT(
    'Microsoft' AS name,
    ['Xbox One', 'Xbox Series X', 'Xbox Series S'] AS consoles
  ) AS vendor_set
  UNION ALL
  SELECT STRUCT(
    'Nintendo' AS name,
    ['Wii U', 'Nintendo Switch', 'Nintendo Switch Lite'] AS consoles
  ) AS vendor_set
  UNION ALL
  SELECT STRUCT(
    'Sony' AS name,
    ['Playstation 3', 'Playstation 4', 'Playstation 5'] AS consoles
  ) AS vendor_set
)
SELECT
  vendor_set.name,
  unnest_console
FROM
  vendors,
  UNNEST(vendor_set.consoles) AS unnest_console;